Coniglio Queries

Overview

This document provides information about Coniglio Queries.

-- Live sessions counter, real time
SELECT
 COUNT(1)
FROM (
 SELECT
  session_uuid,
  max(track_events.index) AS maxindex,
  event_class
 FROM
  track_events
 WHERE
  created_at >= (now() - INTERVAL 5 MINUTE)
--   and tenant_id = 1558
 GROUP BY
  session_uuid)
WHERE
 event_class NOT IN("App\\Events\\SessionEnd", "App\\Events\\TabHidden");

-- Sessions per day
SELECT count(*) AS sessions, FLOOR(SUM(seconds_reading)/60) AS minutes_reading, DATE_FORMAT(started_at,"%Y-%m-%d") AS DAY FROM sessions WHERE tenant_id = 23 GROUP BY day
-- Sessions per month
SELECT count(*) AS sessions, FLOOR(SUM(seconds_reading)/60) AS minutes_reading, DATE_FORMAT(started_at,"%Y-%m") AS month FROM sessions GROUP BY month;
-- Sessions last month
SELECT COUNT(*) as sessions_last_month FROM sessions WHERE started_at BETWEEN (CURRENT_DATE() - INTERVAL 1 MONTH) AND CURRENT_DATE() GROUP BY user_id;
-- Average sessions count per user in time range
SELECT
    AVG(avg_sessions.count) AS avg_sessions_per_user
FROM
    (
        SELECT
            COUNT(*) as count
            FROM sessions
        WHERE
            started_at BETWEEN DATE_FORMAT(NOW(), '%Y-01-01')
            AND DATE_FORMAT(NOW(), '%Y-07-01')
        GROUP BY
            user_id) AS avg_sessions;
-- Average sessions duration in time range, with filtered seconds_reading
SELECT
    count(*) AS sessions,
    AVG(seconds_reading) / 60 AS avg_minutes_reading
FROM
    sessions
WHERE
    started_at BETWEEN DATE_FORMAT(NOW(), '%Y-01-01')
    AND DATE_FORMAT(NOW(), '%Y-07-01')
    AND seconds_reading BETWEEN 5 AND 7200;

---